import { query } from '../config/database.js'
import mysql from 'mysql2/promise'
import { dbConfig } from '../config/database.js'

export const createRoundsTable = async () => {
  const sql = `
    CREATE TABLE IF NOT EXISTS rounds (
      id INT AUTO_INCREMENT PRIMARY KEY,
      match_id INT NOT NULL,
      round_number INT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
  `
  
  try {
    await query(sql)
    console.log('Rounds table created or already exists')
  } catch (error) {
    console.error('Error creating rounds table:', error)
    throw error
  }
}

export const createRound = async (matchId, roundNumber) => {
  const sql = 'INSERT INTO rounds (match_id, round_number) VALUES (?, ?)'
  try {
    const result = await query(sql, [matchId, roundNumber])
    return result.insertId
  } catch (error) {
    console.error('Error creating round:', error)
    throw error
  }
}

export const getRoundsByMatchId = async (matchId) => {
  const sql = 'SELECT * FROM rounds WHERE match_id = ? ORDER BY round_number'
  try {
    return await query(sql, [matchId])
  } catch (error) {
    console.error('Error getting rounds:', error)
    throw error
  }
}

export async function createDetailedScoresTable() {
  try {
    const connection = await mysql.createConnection(dbConfig)
    await connection.query(`
      CREATE TABLE IF NOT EXISTS detailed_scores (
        id INT AUTO_INCREMENT PRIMARY KEY,
        match_id INT NOT NULL,
        player_id INT NOT NULL,
        round_id INT NOT NULL,
        cube_type_id INT NOT NULL,
        score DECIMAL(10,2),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (match_id) REFERENCES matches(id),
        FOREIGN KEY (player_id) REFERENCES players(id),
        FOREIGN KEY (round_id) REFERENCES rounds(id),
        FOREIGN KEY (cube_type_id) REFERENCES cube_types(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    `)
    await connection.end()
    console.log('Detailed scores table created successfully')
  } catch (error) {
    console.error('Error creating detailed_scores table:', error)
    throw error
  }
}

export const createScoresTable = async () => {
  try {
    const connection = await mysql.createConnection(dbConfig)
    await connection.query(`
      CREATE TABLE IF NOT EXISTS scores (
        id INT AUTO_INCREMENT PRIMARY KEY,
        round_id INT NOT NULL,
        table_number INT NOT NULL,  -- 桌号
        home_player_id INT,         -- 主场选手ID
        away_player_id INT,         -- 客场选手ID
        home_score INT DEFAULT 0,   -- 主场选手成绩
        away_score INT DEFAULT 0,   -- 客场选手成绩
        home_unsolved INT DEFAULT 0, -- 主场选手未完成数
        away_unsolved INT DEFAULT 0, -- 客场选手未完成数
        home_penalty INT DEFAULT 0,  -- 主场选手罚时
        away_penalty INT DEFAULT 0,  -- 客场选手罚时
        winner_id INT,              -- 胜者ID
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (round_id) REFERENCES rounds(id),
        FOREIGN KEY (home_player_id) REFERENCES players(id),
        FOREIGN KEY (away_player_id) REFERENCES players(id),
        FOREIGN KEY (winner_id) REFERENCES players(id)
      )
    `)
    connection.end()
    console.log('Scores table created successfully')
  } catch (error) {
    console.error('Error creating scores table:', error)
    throw error
  }
}

export const createMatchPlayersTable = async () => {
  try {
    const connection = await mysql.createConnection(dbConfig)
    await connection.query(`
      CREATE TABLE IF NOT EXISTS match_players (
        id INT AUTO_INCREMENT PRIMARY KEY,
        match_id INT NOT NULL,
        player_id INT NOT NULL,
        school_id INT NOT NULL,     -- 所属学校ID
        draw_number INT NOT NULL,   -- 抽签号
        seat_number INT NOT NULL,   -- 座位号（1-10）
        is_home BOOLEAN NOT NULL,   -- 是否为主场选手
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (match_id) REFERENCES matches(id),
        FOREIGN KEY (player_id) REFERENCES players(id),
        FOREIGN KEY (school_id) REFERENCES schools(id),
        UNIQUE KEY unique_match_player (match_id, player_id),
        UNIQUE KEY unique_match_seat (match_id, school_id, seat_number)
      )
    `)
    connection.end()
    console.log('Match players table created successfully')
  } catch (error) {
    console.error('Error creating match players table:', error)
    throw error
  }
} 